# users in my locations
select u.session_id, u.username, in1.loc_id, 'location' as rel_type, '' as rel_param
from users u, in_loc in1
where in1.loc_id = '$loc_id' and 
      in1.user_id <> '$user_id' and
      in1.user_id = u.user_id

union all
# users in nearby locations
select u.session_id, u.username, in1.loc_id, 'location' as rel_type, '' as rel_param
from users u, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      in1.loc_id = lr.neighbor_id and
      in1.user_id = u.user_id

union all
# friends in my location
select u.session_id, u.username, in1.loc_id, 'friend' as rel_type, '' as rel_param
from users u, friends f, in_loc in1
where in1.loc_id = '$loc_id' and
      f.user_id = '$user_id' and
      f.friend_id = in1.user_id and
      f.friend_id = u.user_id

union all
# friends in nearby locations
select u.session_id, u.username, in1.loc_id, 'friend' as rel_type, '' as rel_param
from users u, friends f, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      f.user_id = '$user_id' and
      f.friend_id = in1.user_id and
      f.friend_id = u.user_id and
      in1.loc_id = lr.neighbor_id

union all
# users who have me listed as a friend in my location
select u.session_id, u.username, in1.loc_id, 'friendrev1' as rel_type, '' as rel_param
from users u, friends f, in_loc in1
where in1.loc_id = '$loc_id' and
      f.friend_id = '$user_id' and
      f.user_id = in1.user_id and
      f.user_id = u.user_id

union all
# users who have me listed as a friend in nearby locations
select u.session_id, u.username, in1.loc_id, 'friendrev1' as rel_type, '' as rel_param
from users u, friends f, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      f.friend_id = '$user_id' and
      f.user_id = in1.user_id and
      f.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id

union all
# friends of friends in my location
select u.session_id, u.username, in1.loc_id, 'fof' as rel_type, u2.username as rel_param
from users u, users u2, friends f, friends f2, in_loc in1
where in1.loc_id = '$loc_id' and
      f.user_id = '$user_id' and
      f.friend_id = f2.user_id and
      f2.friend_id = in1.user_id and
      f2.friend_id = u.user_id and
      f2.user_id = u2.user_id

union all
# friends of friends in nearby locations
select u.session_id, u.username, in1.loc_id, 'fof' as rel_type, u2.username as rel_param
from users u, users u2, friends f, friends f2, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      f.user_id = '$user_id' and
      f.friend_id = f2.user_id and
      f.friend_id = in1.user_id and
      f.friend_id = u.user_id and
      in1.loc_id = lr.neighbor_id

union all
# users with common interests in my location
select u.session_id, u.username, in1.loc_id, 'interest' as rel_type, i2.interest as rel_param
from users u, interests i1, interests i2, in_loc in1
where in1.loc_id = '$loc_id' and
      i1.user_id = '$user_id' and
      i1.interest = i2.interest and
      i2.user_id <> '$user_id' and
      i2.user_id = in1.user_id and
      i2.user_id = u.user_id

union all
# users with common interests in nearby locations
select u.session_id, u.username, in1.loc_id, 'interest' as rel_type, i2.interest as rel_param
from users u, interests i1, interests i2, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      i1.user_id = '$user_id' and
      i1.interest = i2.interest and
      i2.user_id <> '$user_id' and
      i2.user_id = in1.user_id and
      i2.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id

union all
# users with common courses in my location
select u.session_id, u.username, in1.loc_id, 'course' as rel_type, c2.course as rel_param
from users u, courses c1, courses c2, in_loc in1
where in1.loc_id = '$loc_id' and
      c1.user_id = '$user_id' and
      c1.course = c2.course and
      c2.user_id <> '$user_id' and
      c2.user_id = in1.user_id and
      c2.user_id = u.user_id

union all
# users with common courses in nearby locations
select u.session_id, u.username, in1.loc_id, 'course' as rel_type, c2.course as rel_param
from users u, courses c1, courses c2, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      c1.user_id = '$user_id' and
      c1.course = c2.course and
      c2.user_id <> '$user_id' and
      c2.user_id = in1.user_id and
      c2.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id


# interest_add
# users with common interests in my location
select u.session_id, u.username, in1.loc_id, 'interest' as rel_type, '$interest' as rel_param
from users u, interests i1, in_loc in1
where in1.loc_id = '$loc_id' and
      i1.interest = '$interest' and
      i1.user_id <> '$user_id' and
      i1.user_id = u.user_id and
      i1.user_id = in1.user_id

union all
# users with common interests in nearby locations
select u.session_id, u.username, in1.loc_id, 'interest' as rel_type, '$interest' as rel_param
from users u, interests i1, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      i1.interest = '$interest' and
      i1.user_id <> '$user_id' and
      i1.user_id = in1.user_id and
      i1.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id


# interest_del
# users with common interests in my location
select session_id, '$username' as username, '$loc_id' as loc_id, 'rel_del' as msg_type, 'interest' as rel_type, '$interest' as rel_param, NOW()
from users u, interests i1, in_loc in1
where in1.loc_id = '$loc_id' and
      i1.interest = '$interest' and
      i1.user_id <> '$user_id' and
      i1.user_id = u.user_id and
      i1.user_id = in1.user_id

union all
# users with common interests in nearby locations
select session_id, '$username' as username, '$loc_id' as loc_id, 'rel_del' as msg_type, 'interest' as rel_type, '$interest' as rel_param, NOW()
from users u, interests i1, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      i1.interest = '$interest' and
      i1.user_id <> '$user_id' and
      i1.user_id = in1.user_id and
      i1.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id


# course_add
# users with common courses in my location
select u.session_id, u.username, in1.loc_id, 'course' as rel_type, '$course' as rel_param
from users u, courses c1, in_loc in1
where in1.loc_id = '$loc_id' and
      c1.course = '$course' and
      c1.user_id <> '$user_id' and
      c1.user_id = u.user_id and
      c1.user_id = in1.user_id

union all
# users with common courses in nearby locations
select u.session_id, u.username, in1.loc_id, 'course' as rel_type, '$course' as rel_param
from users u, courses c1, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      c1.course = '$course' and
      c1.user_id <> '$user_id' and
      c1.user_id = in1.user_id and
      c1.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id


# course_del
# users with common courses in my location
select session_id, '$username' as username, '$loc_id' as loc_id, 'rel_del' as msg_type, 'course' as rel_type, '$course' as rel_param, NOW()
from users u, courses c1, in_loc in1
where in1.loc_id = '$loc_id' and
      c1.course = '$course' and
      c1.user_id <> '$user_id' and
      c1.user_id = u.user_id and
      c1.user_id = in1.user_id

union all
# users with common courses in nearby locations
select session_id, '$username' as username, '$loc_id' as loc_id, 'rel_del' as msg_type, 'course' as rel_type, '$course' as rel_param, NOW()
from users u, courses c1, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      c1.course = '$course' and
      c1.user_id <> '$user_id' and
      c1.user_id = in1.user_id and
      c1.user_id = u.user_id and
      in1.loc_id = lr.neighbor_id


# friend_add (for new friends of friends based on a new friend)
# friends in my location
select u.session_id, u.username, in1.loc_id
from users u, friends f, in_loc in1
where in1.loc_id = '$loc_id' and
      f.user_id = '$friend_id' and
      f.friend_id = in1.user_id and
      f.friend_id = u.user_id

union all
# friends in nearby locations
select u.session_id, u.username, in1.loc_id
from users u, friends f, in_loc in1, loc_rel lr
where lr.loc_id = '$loc_id' and
      f.user_id = '$friend_id' and
      f.friend_id = in1.user_id and
      f.friend_id = u.user_id and
      in1.loc_id = lr.neighbor_id
